package com.wangcanxuan.www.util;

import com.wangcanxuan.www.util.pool.AbstractConnectionPool;
import com.wangcanxuan.www.util.pool.constant.PooledConstants;
import com.wangcanxuan.www.util.pool.PooledConnection;
import com.wangcanxuan.www.util.pool.factory.CheckConnectionThreadFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.*;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantReadWriteLock;
import java.util.logging.Logger;

/**
 * 连接池工具类,使用单例模式，确保只有一个数据库连接池
 *
 * @author HHLJ
 * @date 2022/03/16
 */
public class ConnectionPoolUtil extends AbstractConnectionPool {
    private static ConnectionPoolUtil connectionPool = null;
    private static ScheduledExecutorService checkConnectionPool;
    private static List<PooledConnection> idleConnections;
    private static List<PooledConnection> activeConnections;
    private static final Lock WRITE_LOCK = new ReentrantReadWriteLock().writeLock();
    private static final Lock READ_LOCK = new ReentrantReadWriteLock().readLock();
    private static final Logger logger = LoggerUtil.getLogger();

    private ConnectionPoolUtil() {
        try {
            createConnectionPool();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建连接池，返回一个工具类对象
     *
     * @return {@link ConnectionPoolUtil}
     */
    public static ConnectionPoolUtil getInstance(){
        WRITE_LOCK.lock();
        try {
            if (connectionPool == null) {
                connectionPool = new ConnectionPoolUtil();
            }
        } finally {
            WRITE_LOCK.unlock();
        }
        return connectionPool;
    }

    /**
     * 得到连接
     *
     * @return {@link Connection}
     */
    @Override
    public Connection getConnection() throws SQLException {
        //当未创建数据库连接池时返回null
        if (connectionPool == null) {
            logger.severe("数据库连接池未创建，无法获得连接");
            return null;
        }
        //获取可用的空闲连接
        WRITE_LOCK.lock();
        try {
            PooledConnection poolConnection = getAvailableConnection();
            while (poolConnection == null) {
                this.wait(PooledConstants.TIME_TO_WAIT);
                poolConnection = getAvailableConnection();
            }
            activeConnections.add(poolConnection);
            poolConnection.setCheckOutTime(System.currentTimeMillis());
            return poolConnection.connection;
        } finally {
            WRITE_LOCK.unlock();
        }
    }

    /**
     * 释放
     *
     * @param preparedStatement 事先准备好的声明中
     * @param resultSet         结果集
     * @param connection        连接
     */
    public void release(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        for (int i = 0; i < size(activeConnections); i++) {
            if (activeConnections.get(i).equals(connection)) {
                returnConnection(activeConnections.get(i));
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                logger.severe(resultSet + "关闭失败" + e.getMessage());
            }
        }
        if(preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                logger.severe(preparedStatement + "关闭失败" + e.getMessage());
            }
        }
    }

    public void closeConnectionPool() throws SQLException {
        if (connectionPool == null) {
            logger.severe("数据库连接池不存在，无法关闭");
            return;
        }
        WRITE_LOCK.lock();
        try {
            logger.info("开始关闭数据库连接池");
            while (activeConnections.size() != 0){
                wait(2000);
                returnConnection(activeConnections.get(0));
            }
            logger.info("激活连接池已清空");
            while (idleConnections.size() != 0) {
                idleConnections.remove(0).connection.close();
            }
            logger.info("空闲连接池已清空");
            connectionPool = null;
            logger.info("数据库连接池关闭成功");
        } finally {
            WRITE_LOCK.unlock();
        }
    }

    /**
     * 返回连接
     *
     * @param poolConnection 连接
     */
    private void returnConnection(PooledConnection poolConnection){
        WRITE_LOCK.lock();
        try {
            activeConnections.remove(poolConnection);
            if (idleConnections.size() < PooledConstants.MAX_IDLE_CONNECTIONS){
                poolConnection.setCheckOutTime(System.currentTimeMillis());
                poolConnection.setIsBusy(false);
                logger.info("连接" + poolConnection.hashCode() + "返回空闲连接池");
                idleConnections.add(poolConnection);
            } else {
                try {
                    logger.info(poolConnection.hashCode() + "数据库连接关闭");
                    poolConnection.connection.close();
                } catch (SQLException e) {
                    logger.severe(poolConnection.hashCode() + "连接关闭失败" + e.getMessage());
                }
            }
        } finally {
            WRITE_LOCK.unlock();
        }
    }

    /**
     * 得到可用的连接
     *
     * @return {@link Connection}
     */
    private PooledConnection getAvailableConnection() throws SQLException {
        PooledConnection connection = null;
        //当激活连接池中连接数大于最大连接数时返回空
        if (activeConnections.size() >= PooledConstants.MAX_ACTIVE_CONNECTIONS) {
            return null;
        }
        //如果空闲连接池不为空，直接获取连接
        WRITE_LOCK.lock();
        try {
            if (isNotEmpty(idleConnections)) {
                connection = idleConnections.remove(0);
                connection.setIsBusy(true);
                logger.info("从空闲连接池中获取连接" + connection.hashCode() + "成功");
            } else {
                // 如果激活连接池还未满
                if (size(activeConnections) < PooledConstants.MAX_ACTIVE_CONNECTIONS) {
                    //如果为空自动创建相应数量的数据库连接
                    createConnections(PooledConstants.INCREMENT_CONNECTIONS);
                    if(isNotEmpty(idleConnections)) {
                        connection = idleConnections.remove(0);
                        connection.setIsBusy(true);
                        logger.info("从空闲连接池中获取连接" + connection.hashCode() + "成功");
                    } else {
                        //这时候证明连接池满了，那么我们要寻找一个超时的连接
                        PooledConnection pooledConnection = activeConnections.get(0);
                        boolean checkOutFlag = System.currentTimeMillis() - pooledConnection.getCheckOutTime() > PooledConstants.MAX_CONNECTION_TIME;
                        if(checkOutFlag) {
                            PooledConnection timeOutConnection = activeConnections.remove(0);
                            logger.info(timeOutConnection.hashCode() + "连接超时");
                            // 未来添加事务回滚
                            timeOutConnection.connection.close();
                            //新建连接
                            connection = new PooledConnection(super.getConnection());
                        }
                    }
                }
            }
        } finally {
            WRITE_LOCK.unlock();
        }
        return connection;
    }

    /**
     * 创建连接池，为idleConnections和activeConnections创建CopyOnWriteArrayList对象
     *
     * @throws SQLException sqlexception异常
     */
    private void createConnectionPool() throws SQLException {
        logger.info("连接池初始化容量：" + PooledConstants.MAX_IDLE_CONNECTIONS);
        logger.info("连接池自动扩容量：" + PooledConstants.INCREMENT_CONNECTIONS);
        logger.info("连接池最大连接数：" + PooledConstants.MAX_ACTIVE_CONNECTIONS);
        logger.info("连接最大超时时间：" + PooledConstants.MAX_CONNECTION_TIME);
        checkConnectionPool = new ScheduledThreadPoolExecutor(2,new CheckConnectionThreadFactory("定时任务"));
        logger.info("检查连接线程池创建成功，核心线程为2");
        testConnections();
        idleConnections = new CopyOnWriteArrayList<>();
        activeConnections = new CopyOnWriteArrayList<>();
        logger.info("数据库连接池初始化成功！");
        createConnections(PooledConstants.MAX_IDLE_CONNECTIONS);
        getSchedule();
    }

    /**
     * 创建新连接
     *
     * @return {@link Connection}
     */
    private PooledConnection newConnection() {
        WRITE_LOCK.lock();
        try {
            PooledConnection poolConnection = null;
            try {
                poolConnection = new PooledConnection(super.getConnection());
                logger.info("连接池创建了新连接：" + poolConnection.hashCode());
            } catch (SQLException e) {
                logger.severe("连接池创建新连接失败" + e.getMessage());
            }
            return poolConnection;
        } finally {
            WRITE_LOCK.unlock();
        }
    }

    /**
     * 按数量创建连接
     *
     * @param numConnections num连接
     */
    private void createConnections(int numConnections) {
        for (int i = 0; i < numConnections; i++) {
            boolean flag = (size(idleConnections) + size(activeConnections)) <= PooledConstants.MAX_ACTIVE_CONNECTIONS;
            WRITE_LOCK.lock();
            try {
                if (flag) {
                    idleConnections.add(newConnection());
                }
            } finally {
                WRITE_LOCK.unlock();
            }
        }
    }

    /**
     * 测试连接是否可用
     */
    private static void testConnections() {
        try {
            if (activeConnections != null && connectionPool.size(activeConnections) > 0){
                for (PooledConnection activeConnection : activeConnections) {
                    testConnectionIsAvailableAndCheckOut(activeConnection);
                }
            }
            if (idleConnections != null && connectionPool.size(idleConnections) > 0) {
                for (PooledConnection idleConnection : idleConnections) {
                    testConnectionIsAvailableAndCheckOut(idleConnection);
                }
            }
        } catch (Exception e) {
            logger.severe("检测异常" + e.getCause());
        }
    }

    /**
     * 测试连接是否可用和超时
     *
     * @param pooledConnection 合用的连接
     */
    private static void testConnectionIsAvailableAndCheckOut(PooledConnection pooledConnection) {
        if (pooledConnection.getIsBusy()) {
            boolean checkOutFlag = System.currentTimeMillis() - pooledConnection.getCheckOutTime() > PooledConstants.MAX_CONNECTION_TIME;
            WRITE_LOCK.lock();
            try {
                if (checkOutFlag) {
                    try {
                        // 检测到超时就移除激活连接池，关闭该连接
                        activeConnections.remove(pooledConnection);
                        connectionPool.wait(PooledConstants.TIME_TO_WAIT);
                        pooledConnection.connection.close();
                        logger.info("系统自动检测到激活连接" + pooledConnection.hashCode() + "超时，已将该连接关闭");
                    } catch (Exception e) {
                        logger.severe("系统检测异常" + e.getCause());
                    }
                }
            } finally {
                WRITE_LOCK.unlock();
            }
        } else  {
            boolean checkOutIdleFlag = System.currentTimeMillis() - pooledConnection.getCheckOutTime() > PooledConstants.MAX_IDLE_CONNECTION_TIME;
            WRITE_LOCK.lock();
            try {
                if (checkOutIdleFlag) {
                    // 检测到该连接超过最大空闲连接时间，关闭该连接并重建连接
                    idleConnections.remove(pooledConnection);
                    logger.info("系统自动检测到空闲连接" + pooledConnection.hashCode() + "超时，已将该连接关闭");
                    idleConnections.add(connectionPool.newConnection());
                }
            } finally {
                WRITE_LOCK.unlock();
            }
            if (!checkOutIdleFlag) {
                extracted(pooledConnection);
            }
        }
    }

    /**
     * 测试连接是否可用
     *
     * @param pooledConnection 合用的连接
     */
    private static void extracted(PooledConnection pooledConnection) {
        Connection connection = pooledConnection.connection;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "SELECT COUNT(*) FROM" + PooledConstants.TEST_TABLE;
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                logger.info("系统自动检测连接" + pooledConnection.hashCode() + "可用");
            } else {
                throw new SQLException("该连接不可用");
            }
        } catch (Exception e) {
            logger.severe("连接" + pooledConnection.hashCode() + "出现异常" + e.getCause());
            try {
                pooledConnection.connection.close();
            } catch (SQLException e1) {
                logger.severe("连接" + pooledConnection.hashCode() + "关闭出现异常" + e1.getCause());
            }
        } finally {
            connectionPool.release(null,preparedStatement,resultSet);
        }
    }

    /**
     * 默认的定时任务，不受配置影响
     */
    private static void maintainConnections() {
        if (connectionPool.size(activeConnections) < PooledConstants.MAX_ACTIVE_CONNECTIONS && connectionPool.size(idleConnections) < PooledConstants.MAX_IDLE_CONNECTIONS) {
            connectionPool.createConnections(PooledConstants.MAX_IDLE_CONNECTIONS - connectionPool.size(idleConnections));
        }
        if (connectionPool.size(idleConnections) > PooledConstants.MAX_IDLE_CONNECTIONS) {
            while (connectionPool.size(idleConnections) > PooledConstants.MAX_IDLE_CONNECTIONS) {
                logger.info("检测出有过多连接在空闲连接池");
                PooledConnection pooledConnection = idleConnections.remove(0);
                try {
                    pooledConnection.connection.close();
                    logger.info("多余连接" + pooledConnection.hashCode() + "被关闭");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 进行定时任务
     */
    private static void getSchedule() {
        int defaultTestConnectionsTime = 10000;
        if (PooledConstants.TEST_CONNECTIONS_TIME >= defaultTestConnectionsTime) {
            checkConnectionPool.scheduleAtFixedRate(() -> {
                try {
                    ConnectionPoolUtil.testConnections();
                } catch (Exception e) {
                    logger.severe("定时检测任务异常" + e.getCause());
                }
            }, 10000,PooledConstants.TEST_CONNECTIONS_TIME, TimeUnit.MILLISECONDS);
        } else {
            checkConnectionPool.scheduleAtFixedRate(() -> {
                try {
                    ConnectionPoolUtil.testConnections();
                } catch (Exception e) {
                    logger.severe("定时检测任务异常" + e.getCause());
                }
            }, 10000,defaultTestConnectionsTime, TimeUnit.MILLISECONDS);
        }

        checkConnectionPool.scheduleAtFixedRate(() -> {
            try {
                ConnectionPoolUtil.maintainConnections();
            } catch (Exception e) {
                logger.severe("定时维系任务异常" + e.getCause());
            }
        },10000,5000,TimeUnit.MILLISECONDS);
    }

    /**
     * 等待连接
     *
     * @param millis 米尔斯
     */
    private void wait(int millis){
        try {
            Thread.sleep(millis);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    /**
     * 大小
     *
     * @param list 列表
     * @return int
     */
    private int size(List<PooledConnection> list) {
        READ_LOCK.lock();
        try {
            return list.size();
        } finally {
            READ_LOCK.unlock();
        }
    }

    /**
     * 不是空的
     *
     * @param list 列表
     * @return boolean
     */
    private boolean isNotEmpty(List<PooledConnection> list){
        READ_LOCK.lock();
        try {
            return !list.isEmpty();
        } finally {
            READ_LOCK.unlock();
        }
    }
}
